package vn.aptech.leaveManagementSystem.business.model;

import java.sql.CallableStatement;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;

import vn.aptech.leaveManagementSystem.business.dto.Employee;
import vn.aptech.leaveManagementSystem.business.dto.UserLoginDTO;
import vn.aptech.leaveManagementSystem.dao.DBManager;

public class Employees extends DBManager{
	public Employees() {
		//setDriver();
	}
	public int addEmployee(Employee emp) throws SQLException
	{
		getConn();
		String sql="{Call sp_InsertEmployeeInfo(?,?,?,?,?,?,?,?,?,?,?,?,?)}";
		CallableStatement cs=conn.prepareCall(sql);
		cs.setString(1, emp.getFirstName());
		cs.setString(2, emp.getLastName());
		cs.setBoolean(3, emp.isGender());
		cs.setDate(4, emp.getBirthday());
		cs.setString(5,emp.getAddress());
		cs.setString(6,emp.getEmail());
		cs.setString(7,emp.getPhone());
		cs.setInt(8, emp.getDepartmentID());
		cs.setInt(9, emp.getPositionID());
		cs.setDate(10, emp.getJoinDate());
		cs.setString(11, emp.getUserName());
		cs.setString(12, emp.getPassword());
		cs.setBoolean(13, emp.isStatusEmp());
		return cs.executeUpdate();
	}
	
	public int updateEmployee(Employee emp) throws SQLException
	{
		getConn();
		String sql="{Call sp_UpdateEmployeeInfo(?,?,?,?,?,?,?,?,?,?,?,?)}";
		CallableStatement cs=conn.prepareCall(sql);
		cs.setInt(1, emp.getIDEmp());
		cs.setString(2, emp.getFirstName());
		cs.setString(3, emp.getLastName());
		cs.setBoolean(4, emp.isGender());
		cs.setDate(5, emp.getBirthday());
		cs.setString(6,emp.getAddress());
		cs.setString(7,emp.getEmail());
		cs.setString(8,emp.getPhone());
		cs.setInt(9, emp.getDepartmentID());
		cs.setInt(10, emp.getPositionID());
		cs.setDate(11, emp.getJoinDate());
		cs.setString(12, emp.getUserName());
		//cs.setString(13, emp.getPassword());
		cs.setBoolean(13, emp.isStatusEmp());
		return cs.executeUpdate();
		
	}
	
	public int updateEmployeePassword(int idEmp,String newPassword) throws SQLException
	{
		getConn();
		String sql="{Call sp_UpdateEmployeePassword(?,?)}";
		CallableStatement cs=conn.prepareCall(sql);
		cs.setInt(1, idEmp);
		cs.setString(2, newPassword);
		return cs.executeUpdate();		
	}
	
	public int deleteEmployee(int idEmp) throws SQLException
	{
		getConn();
		String sql="{Call sp_DeleteEmployee(?)}";
		CallableStatement cs=conn.prepareCall(sql);
		cs.setInt(1, idEmp);
		return cs.executeUpdate();	
	}
	
	public ResultSet getEmployee(int idEmp) throws SQLException
	//Employee.*,DepartmentName,PositionName
	{
		getConn();
		String sql="{Call sp_getEmployee(?)}";
		CallableStatement cs=conn.prepareCall(sql);
		cs.setInt(1, idEmp);
		ResultSet rs=cs.executeQuery();
		return rs;
	}
	
	public ResultSet getEmployeesListForAdmin() throws SQLException
	//Employee.*,DepartmentName,PositionName
	{
		getConn();
		String sql="{Call sp_showEmployeesListForAdmin}";
		CallableStatement cs=conn.prepareCall(sql);
		ResultSet rs=cs.executeQuery();
		return rs;
	}
	
	
	public ResultSet getEmployeesListForDepartmentManager(int idDep) throws SQLException
	//Employee.*,DepartmentName,PositionName
	{
		getConn();
		String sql="{Call sp_showEmployeesListForDepartmentManager(?)}";
		CallableStatement cs=conn.prepareCall(sql);
		cs.setInt(1, idDep);
		ResultSet rs=cs.executeQuery();
		return rs;
	}
	
	
	public ResultSet getEmployeesListInDepartment(int idDep) throws SQLException
	//Employee.*,DepartmentName,PositionName
	{
		getConn();
		String sql="{Call sp_showEmployeesListInDepartment(?)}";
		CallableStatement cs=conn.prepareCall(sql);
		cs.setInt(1, idDep);
		ResultSet rs=cs.executeQuery();
		return rs;
	}
	
	public ResultSet getDepartmentManagerOfThisEmployee(int idEmp) throws SQLException
	//Employee.*
	{
		getConn();
		String sql="{Call sp_getDepartmentManagerOfThisEmployee(?)}";
		CallableStatement cs=conn.prepareCall(sql);
		cs.setInt(1, idEmp);
		ResultSet rs=cs.executeQuery();
		return rs;
	}
	
	public ResultSet getAdmin() throws SQLException
	//Employee.*
	{
		getConn();
		String sql="{Call sp_getAdmin}";
		CallableStatement cs=conn.prepareCall(sql);
		ResultSet rs=cs.executeQuery();
		return rs;
	}
	
	public ResultSet sp_getDirector() throws SQLException
	//Employee.*
	{
		getConn();
		String sql="{Call sp_getAdmin}";
		CallableStatement cs=conn.prepareCall(sql);
		ResultSet rs=cs.executeQuery();
		return rs;
	}
	
	
	public int getLeaveBalanceInThisTime(int idEmp) throws SQLException
	{
		int totalDaysLeave=getTotalDaysLeaveInThisYear(idEmp);
		MyModel myModel=new MyModel();
		int maxNumDaysAllow=myModel.configurations.getMaxNumDaysAllowLeaving();
		return maxNumDaysAllow-totalDaysLeave;
	}
	
	public int getTotalDaysLeaveInThisYear(int idEmp) throws SQLException
	{
		int currentYear=new java.util.Date().getYear();
		java.sql.Date fromDate=new Date(currentYear,1,1);
		java.sql.Date toDate=new Date(currentYear,12,31);
		MyModel myModel=new MyModel();
		ResultSet rs=myModel.reports.getReportTotalDaysLeaveIndividual(idEmp, fromDate, toDate);
		int totalDaysLeave=0;
		if (rs.next())
		{
			totalDaysLeave=rs.getInt("SumDaysLeave");
		}
		return totalDaysLeave;
	}
	
	public ResultSet searchEmployeeForAdmin(Object idDep,Object idPos, Object firstName, Object lastName) throws SQLException
	//Employee.*,DepartmentName,PositionName
	{
		if (firstName==null) 
			firstName="";
		if (lastName==null)  
			lastName="";
		getConn();
		String sql="{Call sp_searchEmployeeForAdmin(?,?,?,?)}";
		CallableStatement cs=conn.prepareCall(sql);
		cs.setObject(1, idDep);
		cs.setObject(2, idPos);
		cs.setObject(3, firstName);
		cs.setObject(4, lastName);
		ResultSet rs=cs.executeQuery();
		return rs;
		//example:
		//searchEmployeeForAdmin(null,null,null,null);
		//searchEmployeeForAdmin(1,null,null,null);
		//searchEmployeeForAdmin(1,4,null,null);
		//searchEmployeeForAdmin(1,4,"tan",null);
		//searchEmployeeForAdmin(1,4,"tan","nguyen");
	}
	
	public ResultSet searchEmployeeForDepartmentManager(Object idDep, Object firstName, Object lastName) throws SQLException
	//Employee.*,DepartmentName,PositionName
	{
		if (firstName==null) 
			firstName="";
		if (lastName==null)  
			lastName="";
		getConn();
		String sql="{Call sp_searchEmployeeForDepartmentManager(?,?,?)}";
		CallableStatement cs=conn.prepareCall(sql);
		cs.setObject(1, idDep);
		cs.setObject(2, firstName);
		cs.setObject(3, lastName);
		ResultSet rs=cs.executeQuery();
		return rs;
		//example:
		//searchEmployeeForDepartmentManager(1,null,null);
		//searchEmployeeForDepartmentManager(1,tan",null);
		//searchEmployeeForDepartmentManager(1,"tan","nguyen");
	}
	
	public ResultSet searchEmployeeForDirector(Object idDep,Object idPos, Object firstName, Object lastName) throws SQLException
	//Employee.*,DepartmentName,PositionName
	{
		if (firstName==null) 
			firstName="";
		if (lastName==null)  
			lastName="";
		getConn();
		String sql="{Call sp_searchEmployeeForDirector(?,?,?,?)}";
		CallableStatement cs=conn.prepareCall(sql);
		cs.setObject(1, idDep);
		cs.setObject(2, idPos);
		cs.setObject(3, firstName);
		cs.setObject(4, lastName);
		ResultSet rs=cs.executeQuery();
		return rs;
		//example:
		//searchEmployeeForDirector(null,null,null,null);
		//searchEmployeeForDirector(1,null,null,null);
		//searchEmployeeForDirector(1,4,null,null);
		//searchEmployeeForDirector(1,4,"tan",null);
		//searchEmployeeForDirector(1,4,"tan","nguyen");
	}
	
	public ResultSet CheckLogin(UserLoginDTO user) throws SQLException
	{
		System.out.println(user.getUserName());
		System.out.println(user.getPassword());
		getConn();
		String sql="{Call sp_CheckLogin(?,?)}";
		CallableStatement cs=conn.prepareCall(sql);
		
		cs.setString(1, user.getUserName());
		cs.setString(2, user.getPassword());
		ResultSet rs=cs.executeQuery();
		return rs;
	}
	
}

